package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MElementValue;
import org.compiere.model.MLocator;
import org.compiere.model.MOrg;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import extend.org.compiere.utils.Util;

public class MM_Form_M44 extends SvrProcess {
	//Form_M44_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	int p_Account_ID, p_M_Locator_ID;
	Date DateAcct;

	/** */
	String ResultPath;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("C_ElementValue_ID")) 
				p_Account_ID = para[i].getParameterAsInt();
			else if (name.equals("M_Locator_ID")) 
				p_M_Locator_ID = para[i].getParameterAsInt();
			else if (name.equals("DateAcct")) 
				DateAcct = (Timestamp) para[i].getParameter();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_M44_";
		Integer pathI = 0;
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xls");
			try { 
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(";
					ResultPath = ResultPath.substring(0, 10) + (++pathI).toString() + ")";  
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" +(++pathI).toString() + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//Cell Style
		//New Cell Style
		CellStyle cs = wb.createCellStyle();
		cs.setBorderBottom((short)1);
		cs.setBorderTop((short)1);
		cs.setBorderLeft((short)1);
		cs.setBorderRight((short)1);
		cs.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		cs.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		
		//Header
		sheet.getRow(7).getCell(0).setCellValue(MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(13).getCell(10).setCellValue((new MElementValue(m_ctx, p_Account_ID, get_TrxName()).getValue()) + "-" + (new MElementValue(m_ctx, p_Account_ID, get_TrxName()).getName()));
		sheet.getRow(16).getCell(2).setCellValue((new MLocator(m_ctx, p_M_Locator_ID, get_TrxName()).getValue()));

		//Position
		Calendar cal = new GregorianCalendar();
		cal.setTime(DateAcct);
		int ai = cal.get(Calendar.MONTH) + 1;
		int row = 21, no = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		for(int i = 0; i < ai; i++){
			cal.set(Calendar.MONTH, i);
			DateAcct = cal.getTime();
		
			String sql = "SELECT \"Product_Number\"" +  
								", \"Product\"" + 
								", \"UOM\"" + 
								", date_trunc('month', timestamp '" + DateAcct + "') AS DateAcct" + 
								", CASE 	WHEN \"RemainQty\" IS NOT NULL THEN \"RemainQty\" " + 
								"		WHEN \"RemainQty\" IS NULL THEN NULL " + 
								" END AS RemainQty " + 
								", CASE 	WHEN \"RemainAmount\" IS NOT NULL THEN \"RemainAmount\" " + 
								"		WHEN \"RemainAmount\" IS NULL THEN NULL " + 
								" END AS RemainAmount " + 
								", sum(\"DebitQty\") AS DebitQty" + 
								", sum(\"DebitAmount\") AS DebitAmount" + 
								", sum(\"CreditQty\") AS CreditQty" + 
								", sum(\"CreditAmount\") AS CreditAmount" + 
							" FROM mm_turnoversheet_m44(" + 
								p_Account_ID + ", " + 
								p_M_Locator_ID + ", '" +
								DateAcct + "') " + 
							" GROUP BY \"Product_Number\", \"Product\", \"UOM\", DateAcct, RemainQty, RemainAmount";
			
			try {
				pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
				rs = pstmt.executeQuery();
				
				while(rs.next()){
					//Shifting and merging
					sheet.shiftRows(row, row + 14, 1);
					sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 1));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 2, 5));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 6, 13));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 14, 16));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 17, 21));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 22, 25));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 26, 30));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 31, 34));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 35, 39));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 40, 43));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 44, 48));
					
					R = sheet.createRow(row);
					for(int j = 0; j < 49; j++){
						C = R.createCell(j);
						C.setCellStyle(cs);
					}
					
					sheet.getRow(row).getCell(0).setCellValue(++no);
					sheet.getRow(row).getCell(2).setCellValue(rs.getString("Product_Number"));
					sheet.getRow(row).getCell(6).setCellValue(rs.getString("Product"));
					sheet.getRow(row).getCell(14).setCellValue(rs.getString("UOM"));
					sheet.getRow(row).getCell(17).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(DateAcct)));
					sheet.getRow(row).getCell(22).setCellValue(rs.getInt("RemainQty"));
					sheet.getRow(row).getCell(26).setCellValue(rs.getInt("RemainAmount"));
					sheet.getRow(row).getCell(31).setCellValue(rs.getInt("DebitQty"));
					sheet.getRow(row).getCell(35).setCellValue(rs.getInt("DebitAmount"));
					sheet.getRow(row).getCell(40).setCellValue(rs.getInt("CreditQty"));
					sheet.getRow(row).getCell(44).setCellValue(rs.getInt("CreditAmount"));

					row++;
				}
			}
			catch (Exception e){
				log.log(Level.SEVERE, sql.toString(), e);
			}
			finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
		
		}
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		// Open the file with the application associated
					Util.launchFile(new File(ResultPath));
				//
		
		return Msg.translate(m_ctx, "Success");
	}

}
